Imports System.Data.SqlClient

Public Class TurnosxPaciente : Implements IBusquedaFiltrado
    Private fecha As Date
    Private idhora As Integer
    Private idtipodoc As Integer
    Private nrodoc As String
    Private matricula As Profesional
    Private matriProf As Integer
    Private nroTurno As Integer
    Private idEstado As Integer
    Private contacto As String
    Public Property contac() As String
        Get
            Return contacto
        End Get
        Set(ByVal value As String)
            contacto = value
        End Set
    End Property
    Public Property estado() As Integer
        Get
            Return idEstado
        End Get
        Set(ByVal value As Integer)
            idEstado = value
        End Set
    End Property
    Public Property matri() As Integer
        Get
            Return matriProf
        End Get
        Set(ByVal value As Integer)
            matriProf = value
        End Set
    End Property
    Public Property NroTur() As Integer

        Get
            Return nroTurno
        End Get
        Set(ByVal value As Integer)
            nroTurno = value
        End Set
    End Property
    Public Property matriculaTA() As Profesional
        Get
            Return matricula
        End Get
        Set(ByVal value As Profesional)
            matricula = value
        End Set
    End Property
    Public Property fechaTP() As Date
        Get
            Return fecha
        End Get
        Set(ByVal value As Date)
            fecha = value
        End Set
    End Property
    Public Property idhoraTP() As Integer
        Get
            Return idhora
        End Get
        Set(ByVal value As Integer)
            idhora = value
        End Set
    End Property
    Public Property tipodocTP() As Integer
        Get
            Return idtipodoc
        End Get
        Set(ByVal value As Integer)
            idtipodoc = value
        End Set
    End Property
    Public Property nrodocTP() As String
        Get
            Return nrodoc
        End Get
        Set(ByVal value As String)
            nrodoc = value
        End Set
    End Property

    Public Function Agregar(ByRef con As ConexionBD) As Boolean Implements IBusquedaFiltrado.Agregar
        Dim comando As SqlCommand = New SqlCommand()
        Dim sql As String = ""

        sql = "INSERT INTO turnosasignados (Fecha,idHora,idtipodoc,nrodoc,matricula,nroturno,idestado,contacto)" + _
                      "  VALUES('{0}',{1},{2},'{3}',{4},{5},{6}, '{7}')"

        sql = String.Format(sql, Format(Me.fechaTP, "dd/MM/yyyy"), Me.idhoraTP, Me.tipodocTP, Me.nrodocTP, Me.matri, Me.NroTur, Me.estado, Me.contac)

        comando = New SqlCommand(sql, con.ConexionBase)
        If comando.ExecuteNonQuery() > 0 Then
            Return True
        Else
            Return False
        End If
    End Function
    Public Function Buscar(ByVal id As Integer, ByRef con As ConexionBD) As Boolean Implements IBusquedaFiltrado.Buscar
        Return True
    End Function
    Public Function Eliminar(ByVal id As Integer, ByRef con As ConexionBD) As Boolean Implements IBusquedaFiltrado.Eliminar
        Return True
    End Function

    Public Function EliminarTur(ByVal id As Integer, ByVal numdoc As String, ByVal nrotur As Integer, ByVal fectur As Date, ByVal mat As Integer, ByRef con As ConexionBD) As Boolean
        Dim sql As String = ""
        Dim comando As SqlCommand = Nothing

        sql = "DELETE FROM TURNOSASIGNADOS WHERE idtipodoc ={0} and nrodoc='{1}' and nroturno={2} and fecha='{3}' and matricula={4}"
        sql = String.Format(sql, id, numdoc, nrotur, Format(fectur, "dd/MM/yyyy"), mat)
        comando = New SqlCommand(sql, con.ConexionBase)
        If comando.ExecuteNonQuery() > 0 Then
            Return True
        Else
            Return False
        End If
    End Function

    Public Function Modificar(ByRef con As ConexionBD) As Boolean Implements IBusquedaFiltrado.Modificar
        Return True
    End Function
    Public Function ModiEstado(ByVal idEstadoTur As Integer, ByRef con As ConexionBD) As Boolean
        Dim sql As String = ""
        Dim comando As SqlCommand = Nothing

        sql = "UPDATE TURNOSASIGNADOS " + _
              "  SET  FECHA= '{0}'," + _
              "      IDHORA ={1}," + _
              "      IDTIPODOC ={2}," + _
              "      NRODOC ='{3}'," + _
              "      MATRICULA ={4}," + _
              "      NROTURNO ={5}," + _
              "      IDESTADO ={6}," + _
              "      CONTACTO='{7}'" + _
              "  WHERE IDTIPODOC = {2} AND NRODOC='{3}'and NROTURNO={5}"
        sql = String.Format(sql, Format(Me.fechaTP, "dd/MM/yyyy"), Me.idhoraTP, Me.tipodocTP, Me.nrodocTP, Me.matri, Me.NroTur, Me.estado, Me.contac)
        comando = New SqlCommand(sql, con.ConexionBase)
        If comando.ExecuteNonQuery() > 0 Then
            Return True
        Else
            Return False
        End If
    End Function
    Public Function BuscarNroTurno(ByVal nrotur As Integer, ByVal fectur As Date, ByRef con As ConexionBD) As Boolean

        Dim comando As SqlCommand
        Dim rs As SqlDataReader
        Dim sql As String
        Dim lResp As Boolean = False

        sql = String.Format("SELECT * FROM TURNOSASIGNADOS WHERE nroturno={0} and fecha='{1}'", nrotur, Format(fectur, "dd/MM/yyyy"))
        comando = New SqlCommand(sql, con.ConexionBase)
        rs = comando.ExecuteReader()
        comando.Dispose()


        If rs.Read Then
            idtipodoc = System.Convert.ToInt16(rs("idtipodoc"))
            nrodoc = rs("nrodoc").ToString
            idhora = rs("idhora")
            matriProf = rs("matricula")
            fecha = rs("fecha")
            nroTurno = rs("nroturno")
            estado = rs("idestado")
            contacto = rs("contacto")

            lResp = True
        Else
            lResp = False
        End If
        rs.Close()
        Return lResp

    End Function
    Public Function BuscarTurnoTNdoc(ByVal id As Integer, ByVal numdoc As String, ByRef con As ConexionBD) As Boolean

        Dim comando As SqlCommand
        Dim rs As SqlDataReader
        Dim sql As String
        Dim lResp As Boolean = False

        sql = String.Format("SELECT * FROM TURNOSASIGNADOS WHERE idtipodoc={0} and nrodoc='{1}'", id, numdoc)
        comando = New SqlCommand(sql, con.ConexionBase)
        rs = comando.ExecuteReader()
        comando.Dispose()


        If rs.Read Then
            idtipodoc = System.Convert.ToInt16(rs("idtipodoc"))
            nrodoc = rs("nrodoc").ToString
            idhora = rs("idhora")
            matriProf = rs("matricula")
            fecha = rs("fecha")
            nroTurno = rs("nroturno")
            estado = rs("idestado")
            contacto = rs("contacto")
            lResp = True
        Else
            lResp = False
        End If
        rs.Close()
        Return lResp
    End Function
    Public Function validarPK(ByVal idHora As Integer, ByVal fecha As Date, ByVal idTipoDoc As Integer, ByVal numDoc As Integer, ByVal matricula As Integer, ByVal nroTurno As Integer, ByRef con As ConexionBD) As Integer
        Dim SQL As String = ""
        Dim comando As SqlCommand
        SQL = "SELECT COUNT(*) FROM TurnosAsignados WHERE Fecha='{0}' AND idHora={1} AND idtipodoc={2} AND nrodoc='{3}' AND matricula={4} AND nroturno ={5}"
        SQL = String.Format(SQL, Format(Me.fechaTP, "dd/MM/yyyy"), Me.idhoraTP, Me.tipodocTP, Me.nrodocTP, Me.matri, Me.NroTur)
        comando = New SqlCommand(SQL, con.ConexionBase)

        Dim count As Integer
        count = comando.ExecuteScalar()
        Return count
    End Function

    Public Sub New()
        fecha = Nothing
        idhora = 0
        idtipodoc = 0
        nrodoc = ""
        matricula = Nothing
        nroTurno = 0
        idEstado = 0
        contacto = ""

    End Sub
End Class